package com.iisigroup.project.statistics;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.Map;

import org.apache.commons.collections.map.HashedMap;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hpsf.SummaryInformation;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

/**
 * Hello world!
 *
 */
public class App 
{
    public static void main( String[] args ) throws Exception
    {
	test02();
    }
    public static void adjustFile(final String srcPath,final String outPath){
	InputStream inp = null;
	HSSFWorkbook wb = null;
	FileOutputStream fileOut = null;
	
	
	try {
	    inp = new FileInputStream(srcPath);
	    wb = new HSSFWorkbook(new POIFSFileSystem(inp));
	    fileOut = new FileOutputStream(outPath);

	    final int numberOfSheets = wb.getNumberOfSheets();

	    for (int i = 0; i < numberOfSheets; ++i) {
		final HSSFSheet sheetSample = wb.getSheetAt(i);
		final int lastRowNum = sheetSample.getLastRowNum();
		final Map<Integer,Integer> columnWidthMap= new HashMap<Integer, Integer>();
		for (int j = 0; j < lastRowNum; ++j) {
		    final HSSFRow currentRow = sheetSample.getRow(j);
		    short lastCellNum = currentRow.getLastCellNum();
		    int maxLines = 0;
		    for (int k = 0; k < lastCellNum; k++) {
			final HSSFCell currentCell = currentRow.getCell(k);			
			//column 為第k個
		        final int defaultColumnWidth = sheetSample.getColumnWidth(k);
		        Integer columnDataWidth =  columnWidthMap.get(k);
		        if(columnDataWidth == null){
		            columnDataWidth = Integer.valueOf(defaultColumnWidth);
		        }
			try {
			    String stringCellValue = currentCell.getStringCellValue();
			    int maxLength = 0 ; 
			    if (StringUtils.contains(stringCellValue, "\n")) {
				final String[] spacers = StringUtils.split(stringCellValue, "\n");
				int lines = spacers.length;
				if (lines > maxLines) {
				    maxLines = lines;
				}
				for(int a =0 ;a< lines ;++a){
				    if(spacers[a].length() > maxLength){
					maxLength = spacers[a].length();
					if(columnDataWidth.intValue() < maxLength){
					    columnDataWidth=Integer.valueOf(maxLength);
					}
				    }
				}
			    }
			} catch (Exception e) {
			}
			columnWidthMap.put(k, columnDataWidth);

		    }
		    short adjustHeight = (short) (maxLines * 240);

		    if (adjustHeight > currentRow.getHeight()) {
			currentRow.setHeight(adjustHeight);
		    }		   
		}
		for(Integer index :columnWidthMap.keySet()){
		    sheetSample.setColumnWidth(index, columnWidthMap.get(index).intValue()  );
		}
	    }

	    // Write the output to a file
	    wb.write(fileOut);
	}  catch (IOException e) {
	    e.printStackTrace();
	}finally{
	    if(fileOut!=null){
		 try {
		    fileOut.close();
		} catch (IOException e) {
		    e.printStackTrace();
		}
	    }
	}
        
       
    }
    private static void test02(){
	
        adjustFile("/home/weblogic/Desktop/Output.xls", "/home/weblogic/Desktop/workbook1.xls");
	
    }
    private static void test01()throws Exception{
	System.out.println( "Hello World!" );
        Workbook wb = new HSSFWorkbook();
        //Workbook wb = new XSSFWorkbook();
        CreationHelper createHelper = wb.getCreationHelper();
        Sheet sheet = wb.createSheet("new sheet");

        // Create a row and put some cells in it. Rows are 0 based.
        Row row = sheet.createRow((short)0);
        // Create a cell and put a value in it.
        Cell cell = row.createCell(0);
        cell.setCellValue(1);

        // Or do it on one line.
       
        row.createCell(1).setCellValue(1.2);
        row.createCell(2).setCellValue(
             createHelper.createRichTextString("This is a string"));
        
        row.createCell(3).setCellValue(true);
        row.setHeight((short)(40*256) );
        
        // Write the output to a file
        FileOutputStream fileOut = new FileOutputStream("/home/weblogic/Desktop/workbook.xls");
        wb.write(fileOut);
        fileOut.close();
    }
}
